
package org.streets.eis.ext.analysis.internal.buildlogic;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.streets.commons.util.GUID;
import org.streets.commons.util.StringUtils;
import org.streets.database.Limits;
import org.streets.database.SQLConnection;
import org.streets.database.SQLNestedException;
import org.streets.database.datadict.ColumnDefinition;
import org.streets.database.datadict.DataDict;
import org.streets.database.datadict.Relation;
import org.streets.database.datadict.TableDefinition;
import org.streets.database.datadict.TableRelations;
import org.streets.eis.ext.analysis.components.ColumnWrapper;
import org.streets.eis.ext.analysis.entities.DisplayField;
import org.streets.eis.ext.analysis.entities.Field;
import org.streets.eis.ext.analysis.entities.JoinTable;
import org.streets.eis.ext.analysis.entities.OrderField;
import org.streets.eis.ext.analysis.entities.QueryStruct;
import org.streets.eis.ext.analysis.entities.WhereField;
import org.streets.eis.ext.analysis.internal.impl.QueryDAOImpl;
import org.streets.eis.ext.analysis.services.QueryDAO;
import org.streets.eis.util.Pager;
import org.streets.wicket.HiveWebApplication;

public class QueryBuilderLogicImpl implements QueryBuilderLogic {
	private static final long serialVersionUID = 1L;
	private Logger logger;
	private QueryDAO dao ;
	private DataDict dict;
	private TableRelations relations;
	private SQLConnection connection;

	public QueryBuilderLogicImpl() {
		HiveWebApplication.get().getRegistry().service(SQLConnection.class);
		this.connection = HiveWebApplication.get().getRegistry().service(SQLConnection.class);
		this.dict = HiveWebApplication.get().getRegistry().service(DataDict.class);
		this.relations = HiveWebApplication.get().getRegistry().service(TableRelations.class);
		this.logger = LoggerFactory.getLogger(QueryBuilderLogicImpl.class);
		this.dao = new QueryDAOImpl(connection);			
	}
	
	//单元测试的需要
	public QueryBuilderLogicImpl(Logger logger, DataDict dict, TableRelations relations, SQLConnection connection) {
		this.dict = dict;
		this.relations = relations;
		this.logger = logger;
		this.connection = connection;
		this.dao = new QueryDAOImpl(connection);		
	}

	public void appendTable(QueryStruct query, String tableName,String alias,Boolean allShow) {
		check(query,tableName,alias);
		doAppendTable(query,tableName,alias,allShow);
	}
	
	private void check(QueryStruct query, String tableName,String alias) {
		TableDefinition table = dict.getTableDef(tableName);
		if(table == null){//不存在			
			throw new SQLNestedException(String.format("table[%s] not found in dictionary",tableName));
		}
		
		if (query.containsTable(tableName)){//已包含
			throw new SQLNestedException(String.format("table[%s] has exist !",tableName));
		}		
		
		String[] tableNames = new String[query.getTables().size() + 1];
		for(int i=0; i < query.getTables().size() ; i++){
			tableNames[i] = query.getTableNames()[i];
		}
		tableNames[query.getTables().size()] = tableName;		 
		if (! isInJoinTreeNodes(tableNames)) {//没关系
			throw new SQLNestedException(String.format("table[%s] has not relations with others !",tableName));			
		}
		
		if (hasSameAlias(query,alias)){
			throw new SQLNestedException(String.format("table[%s]'s alias[%s]  is  reduplicate !",tableName,alias));							
		}
		
	}
	
	private boolean hasSameAlias(QueryStruct query,String alias){
		for(JoinTable table : query.getTables()){
			if ((! (StringUtils.isEmpty(table.getAlias()))) 
				  &&
			    (! (StringUtils.isEmpty(alias)))) {
				if ( table.getAlias().equalsIgnoreCase(alias)){
					return true;
				}
			}
		}
		
		return false;
	}

	public void doAppendTable(QueryStruct query, String tableName,String alias,Boolean allShow) {
		TableDefinition table = dict.getTableDef(tableName);
		JoinTable joinTable = new JoinTable(table,alias,allShow);
		
		query.getTables().add(joinTable);	
		
	}
		
	/**
	 * 删除选定表，对应删除一系列的条件
	 */
	public void removeTable(QueryStruct query, String tableName){
		
		String[] table_names = query.getTableExpr().split(",");
		List<String> names = new ArrayList<String>();
		for (String t_n : table_names) {
			if (t_n.equalsIgnoreCase(tableName)) {
				continue;
			}
			names.add(t_n);
		}
		
		query.setTableExpr(StringUtils.ToDelimitedString(names, ","));
		
		//display
		for (int i = query.getDisplayFields().size()-1; i >= 0; i--) {
			if (query.getDisplayFields().get(i).getTableCode().equalsIgnoreCase(tableName)) {
				query.getDisplayFields().remove(i);
			}
		}
		//Order
		for (int i = query.getOrderFields().size()-1; i >= 0; i--) {
			if (query.getOrderFields().get(i).getTableCode().equalsIgnoreCase(tableName)) {
				query.getOrderFields().remove(i);
			}
		}
		
		// where field
		for (int i = query.getWhereFields().size()-1; i >= 0; i--) {
			if (query.getWhereFields().get(i).getTableCode().equalsIgnoreCase(tableName)) {
				query.getWhereFields().remove(i);
			}
		}
		
		//条件之间默认为"and"关系
		String field_expr = buildFieldExpr(query.getWhereFields(), "and");
		query.setConditionExpr(field_expr);
	}
	
	public String buildFieldExpr(List<WhereField> whereFields, String condition){

		StringBuilder sb = new StringBuilder();
		for (int i = 0; i < whereFields.size(); i++){
			sb.append(whereFields.get(i).getAlias() + " "+condition+" ");
		}
		if (sb.length() > condition.length()) {
		  sb.setLength(sb.length() - condition.length() - 1);
		}
		
		return sb.toString();
	}

	private boolean isInJoinTreeNodes(String[] selectedTables){
		//从全局缓存中取得？否则次次得计算
		//List<Queue> qs = getEntityGroups;
		List<Queue> qs = groupEntity();
		//cleanCodeTable(selectedEntities);//去掉代码表
		List<String> allEntities = findTables();
		//cleanCodeTable(entities);//去掉代码表 
		int[] indexs = matrixIndexs(selectedTables, allEntities.toArray(new String[0]));		
		for(Queue q :qs){
			int[] group = new int[q.getLength()];
			int i = 0;
			while (! q.isEmpty()) {
				group[i] = ((Integer)q.deQueue()).intValue();
				i++;
			}			
			if (isAllInGroup(indexs,group)){
				return true;
			}
		}		
		return false;
	}

	
	private boolean isAllInGroup(int[] indexs,int[] group){
		int count = 0;
		for (int i=0 ; i<indexs.length ; i++){
			for (int j=0 ; j<group.length ; j++){
				if (indexs[i]==group[j]){
					count++;
				}
			}
		}
		
		if (count == indexs.length){
			return true;
		}
		
		return false;
		
	}
	
	public boolean isCodeTable(TableDefinition t){
	  for(ColumnDefinition col : t.getColumns()){
		  if (col.getAttr().length() > 1){
			  return true;
		  }
	  }
	  
	  return false;
	}
	
	
	private boolean isRoot(String table, List<Relation> ers){		
		for(Relation er:ers){
			//不作为其他表的子表的表，则为根表
			if (er.getTargetTable().equalsIgnoreCase(table)){
				return false;
			}
		}
	
		return true;	
	}
	
	
	private int[] matrixIndexs(String[] selectedEntities, String[] allEntities) {
		
		int[] ind =new int[selectedEntities.length];
		
		for(int k=0 ;k < selectedEntities.length;k++){
			String selected = selectedEntities[k];
			for(int i=0; i < allEntities.length ;i++){
				String tmp =  allEntities[i];
				if(tmp.equalsIgnoreCase(selected)){
				  ind[k] = i;	
				}
			}
		}
		
		return ind;
		
	}
	
	public List<Queue> groupEntity() {
		List<String> entities = findTables();
		//cleanCodeTable(entities);//去掉代码表 
		
		List<Relation> ers = relations.getRelations();
		List<Queue> list = groupEntity(entities, ers);//分组结果
		
		return list;
	}

	private List<Queue> groupEntity(List<String> entities, List<Relation> ers) {
		List<Queue> qs = new ArrayList<Queue>();
		int[][] adjMatrix = Matrix.createMatrix(entities,ers);//create邻接矩阵
		
		Graph.hasVisit = new boolean[entities.size()];
		for(int begin = 0; begin < entities.size(); begin++)//所有顶点
		{
			
			//先找出几个根结点来，从根节点开始进行遍历
			if ((! Graph.hasVisit[begin])&&(isRoot(entities.get(begin),ers)))
			{
				Graph.Q_DFS.clean();
				Graph.traverse_DFS(adjMatrix, begin);
				Queue q = Graph.Q_DFS.clone();//结果:一个联通图
				qs.add(q);
			}
		}

		return qs;
	}

	private List<Queue> getEntityGroups(){
		//从全局缓存中取得？否则次次得计算
		List<Queue> qs = groupEntity();
		return qs;
	}

	
	public String buildFromClause(String[] tableCodes) {
		//构造Join语句的方法：		
		//（1）优化树结构:
		//	 A:根据选定的表【子节点】，分析树，砍掉不必要的节点【如同级节点】，生成一个新树;
		//（2）先根按边遍历树，形成Join语句:
		//【2个节点：有相同的父节点；或者是父子关系；或者其父节点有对照关系】
		
		//1.取所在的组
		int[] group = getIncludedGroup(tableCodes);
		
		//2.优化:
		//算法:按照遍历图的顺序构造一个棵树,记录节点的level值,按照Level进行优化
		//（1）小于最小level【t11,t10后面的点】可以去掉；
		//（2）同级同父的节点不是，则去掉；
		//（3）向父类推进，有其他枝节点，遍历去掉；
		//总结：（1）从最小level开始，循环每个选定的节点；遍历子树，去掉不包含选定节点的子枝；
		//TODO ....可能是最短路径算法;简单优化的算法？
		//Optimize(group);
		//3.build join clause
		String clause = buildFromClause(group);		
	 
		return clause;
	 
	}
	
	private int[] getIncludedGroup(String[] selectedEntities) {

		List<Queue> allgroup = getEntityGroups();
		//cleanCodeTable(selectedEntities);//去掉代码表
		
		List<String> entities = findTables();
		//cleanCodeTable(entities);//去掉代码表 
				
		int[] indexs = matrixIndexs(selectedEntities,entities.toArray(new String[0]));

		
		for(Queue q :allgroup){
			int[] group = new int[q.getLength()];
			int i = 0;
			while (! q.isEmpty()) {
				group[i] = ((Integer)q.deQueue()).intValue();
				i++;
			}
			
			if (isAllInGroup(indexs,group)){
				return group;
			}
		}
			
		return null;
	}

	/*
	 *  t1-->t2-->t3
       	+	 +--->t4
  		+--->t5
      ===》【转换】
      er1_2-->er2_3-->er2-4-->er1-5

     from er1-2.parentTable join er1-2.childTable on er1-2.parentField = er1-2.childField
     join er2-3.childTable on er2-3.parentField = er2-3.childField
     join er2-4.childTable on er2-4.parentField = er2-4.childField
     join er1-5.childTable on er1-5.parentField = er1-5.childField
     
     queue里面压入[前一个，当前]
     
  */
	private String buildFromClause(int[] group){
		//group里面放的是Entity在List<Entity>中的索引 //【转换为关系树】
		List<Relation> ers = new ArrayList<Relation>();
		List<String> preEntities = new ArrayList<String>();
		List<String> allEntities = convertEntity(group);
		
		//查找并构造group里的Entity之间的关系列表
		preEntities.add(allEntities.get(0));//初始化第一个
		for(int i=1; i < allEntities.size();i++){
			Relation er = findER(allEntities.get(i),preEntities);//肯定有关系，没关系不会出现;【关系需要转换】对对照表的判断分析??
			ers.add(er);
			preEntities.add(allEntities.get(i));
		}
		
		return buildFromClause(ers);
	}
	
	
	private List<String> convertEntity(int[] group){
		List<String> list = new ArrayList<String>();
		List<String> entities = findTables();
		//cleanCodeTable(entities);//去掉代码表
		for(int i=0;i<group.length;i++){
			int index = group[i];
			list.add(entities.get(index));
		}
			
		return list;
	}
	
	
	private Relation findER(String tableName,List<String> tableNames){
		for(String t_n : tableNames){
			Relation er = findER(tableName, t_n);//这里应该把ER里面的父子关系转换一下；sql就好写了
			if (er != null){
				return er;
			}
		}
		
		return null;
	}
	
	private Relation findER(String tableName1 ,String tableName2){
		List<Relation> ers = relations.getRelations();
		//查找关系
		for(Relation r : ers){
			if (((r.getHostTable().equalsIgnoreCase(tableName2))&&(r.getTargetTable().equalsIgnoreCase(tableName1)))
				||
				((r.getHostTable().equalsIgnoreCase(tableName1))&&(r.getTargetTable().equalsIgnoreCase(tableName2)))){				

				return r;
				
			}
		}
			
		return null;
	}
	
	private String reverse(String erKind){
		//多对一 转为 一对多.................
		//many2one-->one2many;
		return null;
	}

	
	private String buildFromClause(List<Relation> ers){
		if (ers.size() > 0)  {
		Relation er0 = ers.get(0);
		String s = "";
		for(int i= 1 ; i<ers.size() ; i++){
			s = s + " join " + ers.get(i).getTargetTable() 
 			      + " on "+ ers.get(i).getHostTable()+"."+ers.get(i).getHostColumn() 
 			      + " = " + ers.get(i).getTargetTable()+"." +ers.get(i).getTargetColumn();
		}
		
		s = " from "  + er0.getHostTable() 
		  + " join "  + er0.getTargetTable() 
		  + " on "    + er0.getHostTable()+"."+er0.getHostColumn()
		  + " = "     + er0.getTargetTable()+"."+er0.getTargetColumn() + s;
		return s;
		}
		else{
			return "";
		}
	}
	
	
	public String buildSql(QueryStruct query) {
		String[] tableCodes = query.getTableExpr().split(",");
		String s2 = buildFromClause(tableCodes);
		
		String where = buildWhereClause(query);
		String order = buildOrderClause(query);
		//处理显示字段中的代码字段
		StringBuilder from = new StringBuilder();
		from.append(s2);
		String select = buildSelectClause(query,from);
		
		return select + " " + from.toString() +" " + where + " " + order;
	}
	
	
	private String buildSelectClause(QueryStruct query,StringBuilder fromClause) {
		List<DisplayField> fields = query.getDisplayFields();
		if (fields.size()==0){
			return "select * ";
		}

		StringBuilder sb = new StringBuilder();
		sb.append("select ");
		int k = 1;
		for (DisplayField field : fields){//注意排好序dispindex
			String fullFieldName = buildFullFieldName(field);
			
			if (field.isCodeField()){//是代码
				String subAlias = "t" + k;//子查询别名
				String subQry = buildSubQry(field.getAttr(),subAlias);//子查询
				
				//添加到from子句
				fromClause.append(" Join " + subQry);
				//TODO:表中存放的是id还是code，需要统一确定
				fromClause.append(" on " + fullFieldName + "=" + subAlias + ".code");
				
				//sb.append(fullFieldName + ",");//这个是代码，可以不显示【如果要显示，必须修改组装模板数据VM的代码】
				sb.append(subAlias + ".name");
				k++;
			}
			else{//不是代码
				sb.append(fullFieldName);
			}
			
			sb.append("," );
		}//for
		
		sb.setLength(sb.length()-1);
		return sb.toString();
	}

	private String buildFullFieldName(DisplayField field){
		String fullFieldName;
		if ((field.getFuncSign()!=null)&&(field.getFuncSign() != "")){
			fullFieldName = field.getFuncSign() +"("+ field.getFieldCode() + "." + field.getFieldCode()+")";
		}else{
			fullFieldName = field.getTableCode() + "." + field.getFieldCode();
		}
		return fullFieldName;
	}	
	
	private String buildSubQry(String codeGroup,String alias){
		
		String s =" (select id,code,name,alias from EIS_CODE_ITEM where group_id='"+codeGroup+"') " + alias;
		
		return s;
	}

	public String buildWhereClause(QueryStruct query) {
		List<WhereField> fields = query.getWhereFields();
		String expr = query.getConditionExpr();//条件间的关系
		if (StringUtils.isEmpty(expr)){
			expr = buildFieldExpr(fields, "and");
			query.setConditionExpr(expr);
		}		
		//替换
		String s = replaceWhereExpr(fields, expr);		
		s = " where " + s;
		return s;
	}
	
	
	private String replaceWhereExpr(List<WhereField> wherefields,String whereExpr){
		String s = whereExpr; 
		for (int i=0; i< wherefields.size() ; i++){
			String alias = wherefields.get(i).getAlias();//别名
			String where = buildWhere(wherefields.get(i));
		    s = s.replaceAll(alias, where);
		}
		return s;
	}
	
	private String buildWhere(WhereField field){
		// TODO:界面提交时要根据数据类型作验证，以保证这里的正确
		String v = "";
		//if (field.isCodeField()){//如果是代码字段
		v = field.getValue();
			//}else{
			//v = field.getDispValue();//[可以赋给value，然后取value的值]
			//}

		String s ;
		if ((field.getOperSign().equalsIgnoreCase("%..")) || (field.getOperSign().equalsIgnoreCase("..%"))
			|| (field.getOperSign().equalsIgnoreCase("%..%"))){ 
		    return s = field.getTableCode()+"."+field.getFieldCode() + " like '" + field.getOperSign().replaceAll("\\.\\.",v) + "' ";
		}
		
		if ((field.getOperSign().equalsIgnoreCase("IS NULL")) 
			|| (field.getOperSign().equalsIgnoreCase("IS NOT NULL"))){ 
			return s = field.getTableCode()+"."+field.getFieldCode() + " " + field.getOperSign() + " ";
		}

		s = field.getTableCode() + "." + field.getFieldCode() + field.getOperSign();
		
		if (field.getDataType().equalsIgnoreCase("string")){
		   return s +"'" + v +"'";
		}else if (field.getDataType().equalsIgnoreCase("integer")){
			return s + v;
		}else if (field.getDataType().equalsIgnoreCase("double")){
			return s + v;
		}else if (field.getDataType().equalsIgnoreCase("date")){
			return s + "to_date('" + v + "','yyyy-mm-dd')";
		}else if (field.getDataType().equalsIgnoreCase("datetime")){
			return s + "to_date('" + v + "','yyyy-mm-dd hh24:mi:ss')";
		}else if (field.getDataType().equalsIgnoreCase("time")){
			//注意这里,数据库中的是Date，带有日期; eg:to_char(timefield,'hh24:mi:ss') > '08:00:00'
			return s = " to_char(" + field.getTableCode() + "." + field.getFieldCode() + ",'hh24:mi:ss')"
			  			+ field.getOperSign()
			  			+ "'" +  v + "'";	
		}else{
			return s + v;
		}
		
	}
	
	
	private String buildOrderClause(QueryStruct query) {
		List<OrderField> fields = query.getOrderFields();
		StringBuilder sb = new StringBuilder();
		if (fields.size() > 0) {
			sb.append("order by  ");
			for (OrderField f : fields){//注意排好序orderIndex
				sb.append(f.getTableCode() + "." + f.getFieldCode() +" "+ f.getOrderType() );
				sb.append("," );
			}
			sb.setLength(sb.length()-1);
		}
		
		return sb.toString();
	}


	private Field findField(String tableName,String fieldName,List<? extends Field> fields){
		for (Field f : fields)
		{
			if ((f.getTableName().equalsIgnoreCase(tableName)) && (f.getFieldName().equalsIgnoreCase(fieldName))){
				return f;
			}
		}

		return null;
	}
	
//	private OrderField findField(String tableName,String fieldName,List<OrderField> fields){
//		for (OrderField f : fields)
//		{
//			if ((f.getTableName().equalsIgnoreCase(tableName)) && (f.getFieldName().equalsIgnoreCase(fieldName))){
//				return f;
//			}
//		}
//
//		return null;
//	}

	private boolean hasExists(Field field,List<? extends Field> fields){
		Field f = findField(field.getTableName(),field.getFieldName(),fields);
		return f != null;
//		if (f == null){
//			return false;
//		}else{
//			return true;
//		}
	}
	
//	private boolean hasExists(OrderField d,List<OrderField> fields){
//		OrderField f = findField(d.getTableCode(),d.getFieldCode(),fields);
//		if (f == null){
//			return false;
//		}else{
//			return true;
//		}
//	}
		

	public void appendDisplayField(QueryStruct query,DisplayField d){
		if (! hasExists(d,query.getDisplayFields())){
			query.getDisplayFields().add(d);
		}	
	}

	private DisplayField createDisplayField(QueryStruct query,String fieldName) {
		//按表名，别名，从给定的表里面查，没有再从数据字典中查
		String[] names = StringUtils.split(fieldName,".");
		
		if ( names.length == 2 ){		
			String tableCodeOrAlias = names[0];
			String fieldCode = names[1];
			ColumnDefinition column = findColumn(query,tableCodeOrAlias,fieldCode);
			return new DisplayField(column);
		}
		

		if ( names.length == 1 ){//只给了字段名
			String fieldCode = names[0];
			ColumnDefinition column = findColumn(query,fieldCode);
			return new DisplayField(column);
			
		}
				
		throw new SQLNestedException(String.format("[%s] format not support by now !",fieldName)); 
	}
	
	private TableDefinition findTable(QueryStruct query,String tableCodeOrAlias){
		for(JoinTable table : query.getTables()){
			if (table.getTableDef().getName().equalsIgnoreCase(tableCodeOrAlias)){				
				return table.getTableDef();
			}
			
			if ( ! (StringUtils.isEmpty(table.getAlias()))){
				if(table.getAlias().equalsIgnoreCase(tableCodeOrAlias)){
					return table.getTableDef();
				}
			}
		}

		throw new SQLNestedException(String.format("table[%s] must add before used !",tableCodeOrAlias)); 
	
	}

	private ColumnDefinition findColumn(QueryStruct query,String tableCodeOrAlias,String fieldCode){
		
		TableDefinition table = findTable(query,tableCodeOrAlias);
		for(ColumnDefinition column : table.getColumns()){
			if (column.getName().equalsIgnoreCase(fieldCode)){
				return column;
			}
		}

		throw new SQLNestedException(String.format("column[%s] is not defined !",tableCodeOrAlias + "." + fieldCode)); 
	}
	
	private ColumnDefinition findColumn(QueryStruct query,String fieldCode){
		List<ColumnDefinition> columns = findSameCodeColumns(query,fieldCode);
		if (columns.size() > 1 ){
			throw new SQLNestedException(String.format("column[%s] is not explicit !",fieldCode)); 		
		}else{
			return columns.get(0);
		}
	}

	private List<ColumnDefinition> findSameCodeColumns(QueryStruct query,String fieldCode){

		List<ColumnDefinition> columns = new ArrayList<ColumnDefinition>();
		
		for(JoinTable table : query.getTables()){
			
			for(ColumnDefinition column : table.getTableDef().getColumns()){
				if (column.getName().equalsIgnoreCase(fieldCode)){
					columns.add(column);
				}
			}
			
		}
		
		return columns;
		
	}
	
	
	private boolean isDuplicate(QueryStruct query,String fieldCode){
		int count = 0;
		for(JoinTable table : query.getTables()){
			
			for(ColumnDefinition column : table.getTableDef().getColumns()){
				if (column.getName().equalsIgnoreCase(fieldCode)){
					count++;
					break;
				}
			}
			
		}
		
		return count > 1;
		
	}
	

 

	/**
	 * 添加显示字段
	 * fieldName : tableName.fieldName or alias.fieldName or fieldName
	 */
	public void appendDisplayField(QueryStruct query,String fieldName){

		DisplayField field = createDisplayField(query,fieldName);
		
		if (! hasExists(field,query.getDisplayFields())){
			query.getDisplayFields().add(field);
		}	
		
	}
	
	
	
	
	/**
	 *从列表中移除选择的显示字段 
	 */
	public void removeDisplayField(QueryStruct query, DisplayField d){
		for(int i=query.getDisplayFields().size()-1;i>=0;i--){
			DisplayField o = query.getDisplayFields().get(i);
			if((o.getTableName().equalsIgnoreCase(d.getTableName()))
				&&
			  (o.getFieldName().equalsIgnoreCase(d.getFieldName()))){
				query.getDisplayFields().remove(i);
			}
		}

		//设置checked=false
		/*for(int i=0 ; i <detail.getJoinTables().size() ; i++){
			if (detail.getJoinTables().get(i).getName().equalsIgnoreCase(d.getTableCode())){
				for(int j=0;j<detail.getJoinTables().get(i).getColumns().size();j++){
					if (detail.getJoinTables().get(i).getColumns().get(j).getName().equalsIgnoreCase(d.getFieldCode())){
						detail.getJoinTables().get(i).getColumns().get(j).setDisplay(false);
					}
				}
			}
		}
		*/
		
	}
	
	private OrderField createOrderField(QueryStruct query,ColumnWrapper w){
		OrderField o = new OrderField(w);
		o.setShowIndex(query.getOrderFields().size() + 1);
		return o;
	}
	
	
	private DisplayField createDisplayField(QueryStruct query,ColumnWrapper w){
		DisplayField d = new DisplayField(w);
		d.setShowIndex(query.getDisplayFields().size() + 1);
		return d;
	}

	public void displayFieldSelectChanged(QueryStruct query,ColumnWrapper w) {
		DisplayField d = createDisplayField(query,w);
		
		if (w.getDisplay()){//selected
			appendDisplayField(query,d);
		}else{//unselected
			removeDisplayField(query,d);
		}			
	}

	private WhereField createWhereField(QueryStruct query,ColumnWrapper c){
		WhereField w = new WhereField(c);
		w.setDataType(c.getType());//数据类型
		//f.getAttr().length() > 1 即是代码组
		w.setAlias(buildWhereAlias(query,w,c));
	
		return w;
	}
	
	private String buildWhereAlias(QueryStruct query,WhereField w,ColumnWrapper f){
		String name = "C" + Integer.toString(query.getWhereFields().size());
		if (! hasExists(name,query.getWhereFields())){
			return name;
		}else{
			for(int i = 0;i < query.getWhereFields().size();i++){
				String alias = "C" + Integer.toString(i);
				if (! hasExists(alias,query.getWhereFields())){
					return alias;
				}
			}
		}
		
		return "";
	}

	private boolean hasExists(String name,List<WhereField> fields){
		for (WhereField w : fields){
			if (name.equalsIgnoreCase(w.getAlias())){
				return true;
			}
		}
		
		return false;
	}
	

	public void removeWhereField(QueryStruct query, WhereField w) {
		//别名唯一，删除别名相同的即可
		for(WhereField d : query.getWhereFields()){
			if (d.getAlias().equalsIgnoreCase(w.getAlias())){
				query.getWhereFields().remove(d);
				break;
			}
		}
		
		String expr = buildFieldExpr(query.getWhereFields(), "and");
		
		query.setConditionExpr(expr);
	}

	
	public void appendWhereField(QueryStruct query, ColumnWrapper f) {
		WhereField w = createWhereField(query,f);
		query.getWhereFields().add(w);
		//缺省用and
		String expr = buildFieldExpr(query.getWhereFields(), "and");
		query.setConditionExpr(expr);
	}
	
	
	
	public void removeOrderField(QueryStruct query,OrderField d){
		for(int i=query.getOrderFields().size()-1;i>=0;i--){
			OrderField o = query.getOrderFields().get(i);
			if((o.getTableCode().equalsIgnoreCase(d.getTableCode()))
				&&
			  (o.getFieldCode().equalsIgnoreCase(d.getFieldCode()))){
				query.getOrderFields().remove(i);
			}
		}
	}
	

	public void orderFieldSelectionChanged(QueryStruct query,ColumnWrapper w) {
		OrderField o = createOrderField(query,w);
		
		if (w.getOrder()){//selected
			appendOrderField(query,o);
		}else{//unselected
			removeOrderField(query,o);
		}	
	}

	public void delete(QueryStruct query) {
		//TODO:能否删除的规则判断
		dao.delete(query);
	}

	private List<String> findTables() {
		List<String> names = new ArrayList<String>(dict.getTableDefs().size());
		for (TableDefinition def : dict.getTableDefs()) {
			names.add(def.getName());
		}
		return names;
	}

	public List<QueryStruct> findQueries() {
		return dao.findQueries();
	}

	private void setShowIndex(QueryStruct query){
		int i = 1;
		for(DisplayField field:query.getDisplayFields()){
			field.setShowIndex(i);//设置顺序，下次取的时候按此排序
			i++;
		}
	
		int j = 1;
		for(WhereField field:query.getWhereFields()){
			field.setShowIndex(j);//设置顺序，下次取的时候按此排序
			j++;
		}
	
		int k = 1;
		for(OrderField field:query.getOrderFields()){
			field.setShowIndex(k);//设置顺序，下次取的时候按此排序
			k++;
		}
	}

	
	public void save(QueryStruct query) {
		setShowIndex(query);
		if(query.getId().equalsIgnoreCase("-1")){
			query.setId(GUID.nextUUID());
			setChildIds(query);
			try{
				dao.insert(query);
			}catch(SQLNestedException ex){
				query.setId("-1");
				throw new RuntimeException(ex.getMessage());
			}
		}else{
			setChildIds(query);
			dao.update(query);
		}
	}
	
	private void setChildIds(QueryStruct query){
		for(WhereField field : query.getWhereFields()){
			field.setId(GUID.nextUUID());
			field.setQueryId(query.getId());
		}
		
		for(DisplayField field:query.getDisplayFields()){
			field.setId(GUID.nextUUID());
			field.setQueryId(query.getId());
		}
		
		for(OrderField field : query.getOrderFields()){
			field.setId(GUID.nextUUID());
			field.setQueryId(query.getId());
		}
	}

	private ResultSet execSql(String sql,Pager pager) {
		if (pager == null){
			return connection.query(sql, Limits.none());
		}
		
		if(pager.getPageSize() <= 0){
			return connection.query(sql, Limits.none());
		}
		
		if(pager.getCurrentPage()==1){//第一页
			long totalRows = connection.count(sql);
			pager.setTotalRows((int)totalRows);
		}
		
		
		return connection.query(sql, Limits.of(pager.getStartRow(), pager.getPageSize()));
		
	}

	public List<ColumnWrapper> loadFields(TableDefinition table) {
		List<ColumnWrapper> list = new ArrayList<ColumnWrapper>();
		for(ColumnDefinition col : table.getColumns()){
			ColumnWrapper wrapper = new ColumnWrapper(col);
			list.add(wrapper);
		}
		
		return list;
	}

	 

	public String findParentTableCode(String tableCode,String fieldCode) {
		List<Relation> ers = relations.getRelations();
		for(Relation r : ers){
			if ((tableCode.equalsIgnoreCase(r.getTargetTable()))
				&&
				(fieldCode.equalsIgnoreCase(r.getTargetColumn()))){
				return r.getHostTable();
			}
		}
		
		return null;
	}
	
	
	public java.io.File exportExcel(QueryStruct query) throws SQLException, IOException{
		//loadQueryDetail(query);//如何保证query的数据是最新的?
		ResultSet rs = execSql(query.getSql(),null);
		List<DisplayField> fields = query.getDisplayFields();
		return doExportExcel(rs,fields,query.getName());
	}
	
	private java.io.File doExportExcel(ResultSet rs,List<DisplayField> fields,String title) throws SQLException, IOException{	
		java.io.File file = new java.io.File("workbook.xls");
		
		HSSFWorkbook wb = new HSSFWorkbook();      
	    HSSFSheet sheet=wb.createSheet(title + "查询结果");  

	    //title
	    HSSFRow tRow = sheet.createRow(0);
	    for(int i = 0 ; i < fields.size(); i++){
	    	HSSFCell cell = tRow.createCell(i);
	    	cell.setCellValue(fields.get(i).getFieldName());
	    }
	    
	    //data 
	  	int r = 1;
	    while(rs.next()) {
	    	HSSFRow dRow = sheet.createRow(r);
		    for(int i = 0 ; i < fields.size(); i++){
		    	HSSFCell cell = dRow.createCell(i);
				cell.setCellValue(rs.getString(i+1));//注意数据类型
		    }
		    r++;
	    }
	    
	    // Write the output to a file   
		FileOutputStream fileOut = new FileOutputStream(file);
		wb.write(fileOut);
		fileOut.close();
			
		return file;
	}

 
 
	public boolean isValid(QueryStruct query, StringBuffer hint) {
		if (StringUtils.isEmpty(query.getTableExpr())){
			hint.append("未选定要查询的表;\n");
		}
		
		if (query.getDisplayFields().size() == 0){
			hint.append("未选定要显示的字段;\n");
		}
		
		if (query.getWhereFields().size() == 0){
			hint.append("未给定查询的条件;\n");
		}
		
		for(WhereField field : query.getWhereFields()){
			if (StringUtils.isEmpty(field.getOperSign())){
				hint.append("条件"+field.getAlias()+"未给定比较符号;\n");
			}
			if (StringUtils.isEmpty(field.getValue())){
				hint.append("条件"+field.getAlias()+"未给定比较值;\n");
			}
		}
		
		if (StringUtils.isEmpty(query.getConditionExpr())){
			hint.append("未给定条件表达式;\n");
		}else{
			//验证条件表达式是否正确? select * from dual where 1=1;将C0,C1,C2,C3替换为1=1即可
			String s = query.getConditionExpr();
			for (int i=0; i< query.getWhereFields().size() ; i++){
					String alias = query.getWhereFields().get(i).getAlias();//别名
					String where = "1=1";
					s = s.replaceAll(alias, where);
			}
			String validSql = "select * from dual where " + s;
			try{
				connection.count(validSql);
			}catch(SQLNestedException e){
				hint.append("给定的条件表达式的逻辑关系不正确;\n");	
			}
		}
		
		if (hint.length() > 0){
			return false;
		}
		
		String sql = buildSql(query);
		try{
		  connection.count(sql);
		  query.setSql(sql);
		}catch(SQLNestedException e){
		  hint.append("sql语句不正确:"+e.getMessage());	
		}
		
		return hint.length() <= 0;
	}

	
	public void appendWhereField(QueryStruct query, String condition) {
		//为什么要解析条件？？解析之后有什么作用？？
		//先不解析条件；或者只作简单的解析，为了展示；		
		
		//1.不解析方案
		query.getConditions().add(condition);		
		//throw new SQLNestedException(String.format("[%s] format not support by now !",condition)); 
	}

	public void removeDisplayField(QueryStruct query, String field) {
		// TODO Auto-generated method stub
		
	}

	public void appendOrderField(QueryStruct query, String fieldName,String orderType) {
		OrderField field = createOrderField(query,fieldName);
		field.setOrderType(orderType);
		
		if (! hasExists(field,query.getOrderFields())){
			query.getOrderFields().add(field);
		}	
		
	}


	private OrderField createOrderField(QueryStruct query,String fieldName) {
		//按表名，别名，从给定的表里面查，没有再从数据字典中查
		String[] names = StringUtils.split(fieldName,".");
		
		if ( names.length == 2 ){		
			String tableCodeOrAlias = names[0];
			String fieldCode = names[1];
			ColumnDefinition column = findColumn(query,tableCodeOrAlias,fieldCode);
			return new OrderField(column);
		}
		

		if ( names.length == 1 ){//只给了字段名
			String fieldCode = names[0];
			ColumnDefinition column = findColumn(query,fieldCode);
			return new OrderField(column);
			
		}
				
		throw new SQLNestedException(String.format("order by[%s] format not support by now !",fieldName)); 
	}
	
	
	public void appendOrderField(QueryStruct query,OrderField field){
		if (! hasExists(field,query.getOrderFields())){
			query.getOrderFields().add(field);
		}	
	}
	


 

 
 
}
